#!/usr/bin/env python3
#utf-8
import re
import poplib
import base64
from email.parser import Parser
# 用来解析邮件主题
from email.header import decode_header
# 用来解析邮件来源
from email.utils import parseaddr
import pymysql
from bs4 import BeautifulSoup
import time
from pathlib import Path
import logging

import sys, os
path = os.path.abspath('../..')+'/ark_project'
sys.path.append(path)  # 会追加到列表最尾部
from settings import mysql_settings
settings=mysql_settings.mysqlSettings()
settings=settings.mysql_conf
MYSQL_HOST=settings['MYSQL_HOST']
MYSQL_PORT=settings['MYSQL_PORT']
MYSQL_DB=settings['MYSQL_DB']
MYSQL_USER=settings['MYSQL_USER']
MYSQL_PASSWD=settings['MYSQL_PASSWD']
MYSQL_CHARTSET=settings['MYSQL_CHARTSET']

class ark_email_spider():
    def __init__(self,log_rank=logging.INFO):
        self.curr_path=os.getcwd()+'/'+'ark_trade/'
        # print('==self.curr_path:',self.curr_path)
        self.log_rank=log_rank
        self.start_date_str = str(time.strftime('%Y-%m-%d', time.localtime()))
        self.logger=self.log_process()
        # self.email_done_date_list=open(self.curr_path+'email_done_date.txt','r',encoding='utf-8').readlines()
        # self.email_done_date_list=[each.strip().replace('\ufeff','') for each in self.email_done_date_list]
        # self.fout=open(self.curr_path+'email_done_date.txt','a',encoding='utf-8')
        if not os.path.exists(self.curr_path+'datas'): os.makedirs(self.curr_path+'datas')
        self.data_path=self.curr_path+'datas/'
        self.email_data_date=''

        self.connect = pymysql.connect(
            host=MYSQL_HOST,
            port=MYSQL_PORT,
            database=MYSQL_DB,
            user=MYSQL_USER,
            password=MYSQL_PASSWD,
            charset=MYSQL_CHARTSET
        )
        self.cursor = self.connect.cursor()

    def log_process(self):
        if not os.path.exists(self.curr_path+'logs'):os.makedirs(self.curr_path+'logs')

        log_path = self.curr_path+'logs/'+Path(__file__).name.replace('.py','')+'_%s.log'%self.start_date_str
        logger = logging.getLogger()
        logger.setLevel(logging.DEBUG)  # or whatever
        handler = logging.FileHandler(log_path, 'w', 'utf-8')  # or whatever
        handler.setFormatter(logging.Formatter(
            '%(asctime)s - %(pathname)s[line:%(lineno)d] - %(levelname)s: %(message)s'))  # or whatever
        logger.addHandler(handler)

        # logging.basicConfig(level=self.log_rank,#控制台打印的日志级别
        #                     filename=self.curr_path+'logs/'+Path(__file__).name.replace('.py','')+'_%s.log'%self.start_date_str,
        #                     filemode='a',##模式，有w和a，w就是写模式，每次都会重新写日志，覆盖之前的日志
        #                     #a是追加模式，默认如果不写的话，就是追加模式
        #                     format=
        #                     '%(asctime)s - %(pathname)s[line:%(lineno)d] - %(levelname)s: %(message)s'
        #                     #日志格式
        #                     )
        # logger=logging.getLogger(__name__)
        return logger

    def get_email_content(self):
        useraccount = 'tradego_ark'
        password = 'ISAQTMOLGMMNYRSC'

        # 邮件服务器地址,以下为网易邮箱
        pop3_server = 'pop.163.com'

        # 开始连接到服务器
        server = poplib.POP3(pop3_server)

        # 打开或者关闭调试信息，为打开，会在控制台打印客户端与服务器的交互信息
        server.set_debuglevel(1)

        # 打印POP3服务器的欢迎文字，验证是否正确连接到了邮件服务器
        self.logger.info(server.getwelcome().decode('utf8'))

        # 开始进行身份验证
        server.user(useraccount)
        server.pass_(password)

        # 返回邮件总数目和占用服务器的空间大小（字节数）， 通过stat()方法即可
        email_num, email_size = server.stat()
        self.logger.info("消息的数量: {0}, 消息的总大小: {1}".format(email_num, email_size))

        # 使用list()返回所有邮件的编号，默认为字节类型的串
        rsp, msg_list, rsp_siz = server.list()
        self.logger.info("服务器的响应: {0},\n消息列表： {1},\n返回消息的大小： {2}".format(rsp, msg_list, rsp_siz))

        self.logger.info('邮件总数： {}'.format(len(msg_list)))

        # 下面单纯获取最新的一封邮件
        total_mail_numbers = len(msg_list)
        rsp, msglines, msgsiz = server.retr(total_mail_numbers)
        # print("服务器的响应: {0},\n原始邮件内容： {1},\n该封邮件所占字节大小： {2}".format(rsp, msglines, msgsiz))

        # msg_content = b'\r\n'.join(msglines).decode('gbk')
        msg_content = b'\n'.join(msglines).decode('utf-8')
        msg_content=msg_content.replace('=\n','')
        # print(msg_content)

        msg = Parser().parsestr(text=msg_content)
        # print('解码后的邮件信息:\n{}'.format(msg))

        # 关闭与服务器的连接，释放资源
        server.close()

        return msg

    def parser_subject(self,msg):
        subject = msg['Subject']
        value, charset = decode_header(subject)[0]
        if charset:
            value = value.decode(charset)
        self.logger.info('邮件主题： {0}'.format(value))
        return value

    def parser_address(self,msg):
        hdr, addr = parseaddr(msg['From'])
        # name 发送人邮箱名称， addr 发送人邮箱地址
        name, charset = decode_header(hdr)[0]
        if charset:
            name = name.decode(charset)
        self.logger.info('发送人邮箱名称: {0}，发送人邮箱地址: {1}'.format(name, addr))
        return name,addr

    def parser_content(self,msg):
        content = msg.get_payload(decode=True)

        # print('==',content)
        soup = BeautifulSoup(content, 'lxml')
        table = soup.select('table')
        tr_list = table[0].select('tr')
        result_list = []
        for tr in tr_list[1:]:
            td_list = tr.select('td')
            td_datas = [td.get_text().replace('\n', '') for td in td_list]
            date = td_datas[2]
            date_list = date.split('/')
            date = date_list[2] + '-' + date_list[0] + '-' + date_list[1]
            td_datas[2] = date
            self.email_data_date=date   #邮件数据的日期，新建文件文件名需要
            td_datas[7]=int(td_datas[7].replace(',',''))
            td_datas[8]=float(td_datas[8])
            td_datas = tuple(td_datas[1:])
            result_list.append(td_datas)
        return result_list

    def get_all_email_content(self):
        useraccount = 'tradego_ark'
        password = 'ISAQTMOLGMMNYRSC'

        # 邮件服务器地址,以下为网易邮箱
        pop3_server = 'pop.163.com'

        # 开始连接到服务器
        server = poplib.POP3(pop3_server)

        # 打开或者关闭调试信息，为打开，会在控制台打印客户端与服务器的交互信息
        server.set_debuglevel(1)

        # 打印POP3服务器的欢迎文字，验证是否正确连接到了邮件服务器
        self.logger.info(server.getwelcome().decode('utf8'))

        # 开始进行身份验证
        server.user(useraccount)
        server.pass_(password)

        # 返回邮件总数目和占用服务器的空间大小（字节数）， 通过stat()方法即可
        email_num, email_size = server.stat()
        self.logger.info("消息的数量: {0}, 消息的总大小: {1}".format(email_num, email_size))

        # 使用list()返回所有邮件的编号，默认为字节类型的串
        rsp, msg_list, rsp_siz = server.list()
        self.logger.info("服务器的响应: {0},\n消息列表： {1},\n返回消息的大小： {2}".format(rsp, msg_list, rsp_siz))

        self.logger.info('邮件总数： {}'.format(len(msg_list)))

        for total_mail_numbers in range(1,len(msg_list)+1):
            rsp, msglines, msgsiz = server.retr(total_mail_numbers)

            msg_content = b'\n'.join(msglines).decode('utf-8')
            msg_content = msg_content.replace('=\n', '')

            msg = Parser().parsestr(text=msg_content)

            yield msg

        # 关闭与服务器的连接，释放资源
        server.close()

    def insert_mysql(self,datas):
        """
        :param datas: [(),(),()]形式的，一次插入多条数据
        :return:
        """

        for data in datas:
            sql='insert into ark_trade(fund,date,direction,ticker,cusip,company,shares_ark,wgtpct_ark,create_time,update_time) values(%s,%s,%s,%s,%s,%s,%s,%s,now(),now())'
            param=data
            try:
                self.cursor.execute(sql,param)
                self.connect.commit()
            except Exception as e:
                self.logger.warning('===trade邮件数据插入失败，%s===',param)
                self.logger.warning(e)


    def run_update_new_email(self):
        # 解析最新的邮件
        msg = self.get_email_content()
        result_list = self.parser_content(msg)
        self.insert_mysql(result_list)

    def run_add_all_email(self):
        #解析历史所有邮件
        sql_select_trade_date="select DISTINCT date from ark_trade;"
        self.cursor.execute(sql_select_trade_date)
        email_done_date_list=self.cursor.fetchall()
        email_done_date_list = [t[0] for t in email_done_date_list]

        msg_iter=self.get_all_email_content()
        for msg in msg_iter:
            msg_date=msg['Date']
            subject_value=self.parser_subject(msg)
            if isinstance(subject_value,bytes):
                subject_value=subject_value.decode('utf-8')
            name,addr=self.parser_address(msg)
            if name=='ARK Trading Desk' and addr=='ark@ark-funds.com' and 'ARK Investment Management LLC' in subject_value:
                result_list = self.parser_content(msg)  #先解析数据，获取数据的日期
                if self.email_data_date not in email_done_date_list:
                    self.insert_mysql(result_list)
                    self.logger.info('----Date:%s 邮件解析完成 数据日期:%s-----',msg_date,self.email_data_date)
                    self.save_txt(result_list)

        self.cursor.close()
        self.connect.close()

    def save_txt(self,result_list):
        #[(),(),()]形式的，一次插入多条数据
        file_name=self.email_data_date+'.txt'
        save_fout=open(self.data_path+file_name,'w',encoding='utf-8')
        for each in result_list:
            each =[str(i) for i in each]
            save_fout.write('   '.join(each)+'\n')
        save_fout.close()

if __name__ == '__main__':
    email_spider=ark_email_spider(log_rank=logging.INFO)
    email_spider.run_add_all_email()

